using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using BLL;

namespace DAL.Managers
{
    /// <summary>
    /// Manager for the object Ebook.
    /// </summary>
    public class EbookManager
    {
        #region properties

        private static EbookManager _sInstance;
        private static readonly object SInstanceLocker = new object();

        #endregion

        /// <summary>
        /// Gets the instance.
        /// </summary>
        /// <value>The instance.</value>
        public static EbookManager Instance
        {
            get
            {
                lock (SInstanceLocker)
                {
                    return _sInstance ?? (_sInstance = new EbookManager());
                }
            }
        }

        #region public functions

        /// <summary>
        /// Gets all books.
        /// </summary>
        /// <param name="conn">The connection to the database.</param>
        /// <returns>All the books of the catalog.</returns>
        public List<Ebook> GetAllBooks(SQLiteConnection conn = null)
        {
            List<Ebook> books;

            if (conn != null)
            {
                // Treatment
                books = GetAllBooksTreatment(conn);
            }
            else
            {
                using (conn = new SQLiteConnection(Connection.CatalogConnection))
                {
                    conn.Open();

                    // Treatment
                    books = GetAllBooksTreatment(conn);
                }
            }

            return books;
        }

        /// <summary>
        /// Gets the last update date.
        /// </summary>
        /// <param name="conn">The connection to the database.</param>
        /// <returns>The last update date for the catalog.</returns>
        public DateTime GetLastUpdateDate(SQLiteConnection conn = null)
        {
            DateTime dt;

            if (conn != null)
            {
                // Treatment
                dt = GetLastUpdateDateTreatment(conn);
            }
            else
            {
                using (conn = new SQLiteConnection(Connection.CatalogConnection))
                {
                    conn.Open();

                    // Treatment
                    dt = GetLastUpdateDateTreatment(conn);
                }
            }

            return dt;
        }

        /// <summary>
        /// Gets the number of books.
        /// </summary>
        /// <param name="conn">The connection to the database.</param>
        /// <returns>The number of books</returns>
        public int GetNbBooks(SQLiteConnection conn = null)
        {
            int nb;

            if (conn != null)
            {
                // Treatment
                nb = GetNbBooksTreatment(conn);
            }
            else
            {
                using (conn = new SQLiteConnection(Connection.CatalogConnection))
                {
                    conn.Open();

                    // Treatment
                    nb = GetNbBooksTreatment(conn);
                }
            }

            return nb;
        }

        #endregion

        #region private functions

        /// <summary>
        /// Gets all books (Main treatment).
        /// </summary>
        /// <param name="conn">The connection to the database (opened).</param>
        /// <returns>The list of the books.</returns>
        private static List<Ebook> GetAllBooksTreatment(SQLiteConnection conn)
        {
            var books = new List<Ebook>();
            
            // First we collect the basic data of the book (id, title, summary, series_index and all the links with the other tables)
            const string query = "SELECT b.id, title, path, a.author, p.publisher, s.series, series_index, c.text, has_cover " +
                                 "FROM books b " +
                                 "LEFT JOIN books_authors_link a ON a.book = b.id " +
                                 "LEFT JOIN books_publishers_link p ON p.book = b.id " +
                                 "LEFT JOIN books_series_link s ON s.book = b.id " +
                                 "LEFT JOIN comments c ON c.book = b.id ";
            var cmd = new SQLiteCommand(query, conn);

            // Construction of the basic books
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                // New book
                var book = new Ebook();
                book.Id = reader.GetInt64(0);
                book.Title = reader.GetString(1);
                book.Folder = reader.GetString(2);
                book.SeriesIndex = reader.GetFloat(6);
                book.Summary = reader[7].ToString();

                // Construct the url for the cover
                if(reader.GetBoolean(8))
                {
                    book.CoverUrl = book.Folder + "/cover.jpg";
                }
                else
                {
                    //Todo : put an empty image
                }

                // Collect the author
                if (!reader.IsDBNull(3)) book.Author = AuthorManager.Instance.GetAuthorById(reader.GetInt64(3), conn);

                // Collect the publisher
                if (!reader.IsDBNull(4)) book.Publisher = PublisherManager.Instance.GetPublisherById(reader.GetInt64(4), conn);

                // Collect the serie if there's one
                if (!reader.IsDBNull(5)) book.Serie = SerieManager.Instance.GetSerieById(reader.GetInt64(5), conn);

                // Collect all the tags
                book.Tags = TagManager.Instance.GetTagsByEbook(book.Id, conn);

                // Collect all the files we can download
                book.Files = new ObservableCollection<EbookFile>(EbookFileManager.Instance.GetFilesByEbook(book.Id, book.Folder, conn));

                // Add the book to the collection
                books.Add(book);
            }

            reader.Dispose();
            cmd.Dispose();

            return books;
        }

        /// <summary>
        /// Gets the last update date (Main treatment).
        /// </summary>
        /// <param name="conn">The connection to the database (opened).</param>
        /// <returns>The last update date of the catalog.</returns>
        private static DateTime GetLastUpdateDateTreatment(SQLiteConnection conn)
        {
            var dt = DateTime.MinValue;

            const string query = "SELECT DATE(timestamp) FROM books ORDER BY timestamp DESC LIMIT 1";
            var cmd = new SQLiteCommand(query, conn);

            var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                //var test = reader[0];
                //test.ToString();
                dt = reader.GetDateTime(0);
            }

            // Clear the memory
            reader.Dispose();
            cmd.Dispose();

            return dt;
        }

        /// <summary>
        /// Gets the number of books (Main treatment).
        /// </summary>
        /// <param name="conn">The connection to the database (opened).</param>
        /// <returns>The number of books</returns>
        private static int GetNbBooksTreatment(SQLiteConnection conn)
        {
            var nb = 0;

            const string query = "SELECT count(id) FROM books";
            var cmd = new SQLiteCommand(query, conn);

            var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                nb = reader.GetInt32(0);
            }

            // Clear the memory
            reader.Dispose();
            cmd.Dispose();

            return nb;
        }

        #endregion

    }
}
